
insert overwrite table jms_dm.dm_door_sign_rate_sum_dt partition ( dt )
select t.dt as date_time,
       max(manage_name) as manage_name,
       max(manage_code) as manage_code,
       max(provider_desc) as provider_desc,
       max(provider_id) as provider_id,
       max(agent_name) as agent_name,
       max(agent_code) as agent_code,
       scan_site_code as scan_site_code,
       max(scan_site_name) as scan_site_name,
       sum(case when is_timely=1 then 1 else 0 end) as is_timely_count,
       sum(case when is_door = 1 then 1 else 0 end) as is_door_count,
       sum(case when is_xusign = 1 then 1 else 0 end) as is_xusign_count,
       sum(case when is_stage_sign = 1 then 1 else 0 end) as is_stage_sign_count,
       sum(case when is_stage_sign = 1 and is_performance_flag = 1 then 1 else 0 end) as is_performance_flag,
       sum(case when is_writeback_flag =1 and pistol_id in (1,2,3,4,5) then 1 else 0 end) as is_stagesignanddoor_count ,
       null as signed_type,
       0 as signed_type_count,
       max(waybill_no) as code,
       '1' as type,
       max(t.ordersource_code) ordersource_code,
       max(t.ordersource_name) ordersource_name,
       sum(case when pistol_id=1 and is_writeback_flag =1 then 1 else 0 end ) as is_stage_cn,
       sum(case when pistol_id=2 and is_writeback_flag =1 then 1 else 0 end ) as is_stage_dd,
       sum(case when pistol_id=3 and is_writeback_flag =1 then 1 else 0 end ) as is_stage_other,
       t.is_reback ,
       max(fran_code) fran_code,
       max(fran_name) fran_name,
       sign_big,
       count(waybill_no) sign_big_count,
       max(t.city_id) as city_id,
       max(t.city_desc) as city_desc,
       max(t.area_id) as area_id,
       max(t.area_desc) as area_desc ,
       sum(case when t.timely_type=2 then 1 else 0 end ) as pp_sign_cn,  --收派员签收票数
       sum(case when t.sign_big='收派员上门' then 1 else 0 end ) as pp_door_cn,  --收派员上门票数
       sum(case when t.sign_big='驿站出库' then 1 else 0 end ) as pp_stage_cn,  --驿站签收票数
       sum(case when pistol_id=4 and is_writeback_flag =1 then 1 else 0 end ) as is_stage_tx,  --送入驿站上门票数【兔喜】
       sum(case when pistol_id=5 and is_writeback_flag =1 then 1 else 0 end ) as is_stage_ll,  --送入驿站上门票数【邻里】
       0 as store_in_total,
       null as scan_user,
       null as scan_user_code,
       null as store_code,
       null as store_name,
       sum(should_door) as should_num,
       sum(case when should_door=1 and is_door=1 then 1 else 0 end ) as should_door_num,

       sum(case when should_door=1 and source='驿站' and !(is_writeback_flag =1 and pistol_id in (1,2,3,4,5)) then 1 else 0 end ) as no_door_yz_num ,
       sum(case when should_door=1 and source='柜机' and !(is_writeback_flag =1 and pistol_id in (1,2,3,4,5)) then 1 else 0 end ) as no_door_jk_num  ,
       max(t.is_dk) as is_dk,
       t.dt
from jms_dm.dm_door_sign_rate_detail_dt t
where    t.dt = '{{ execution_date | cst_ds }}'
     group by t.dt,t.manage_code,t.agent_code,t.provider_id,t.scan_site_code ,t.ordersource_code,t.ordersource_name,t.is_reback,sign_big,city_id,area_id
union all
select t.dt as date_time,
       max(manage_name) as manage_name,
       max(manage_code) as manage_code,
       max(provider_desc) as provider_desc,
       max(provider_id) as provider_id,
       max(agent_name) as agent_name,
       max(agent_code) as agent_code,
       scan_site_code as scan_site_code,
       max(scan_site_name) as scan_site_name,
       sum(case when is_timely=1 then 1 else 0 end) as is_timely_count,
       sum(case when is_door = 1 then 1 else 0 end) as is_door_count,
       sum(case when is_xusign = 1 then 1 else 0 end) as is_xusign_count,
       sum(case when is_stage_sign = 1 then 1 else 0 end) as is_stage_sign_count,
       sum(case when is_stage_sign = 1 and is_performance_flag = 1 then 1 else 0 end) as is_performance_flag,
       sum(case when is_writeback_flag =1 and pistol_id in (1,2,3,4,5) then 1 else 0 end) as is_stagesignanddoor_count ,
       signed_type as signed_type,
       count(1) as signed_type_count,
       max(waybill_no) as code,
       '2' as type,
       ordersource_code as ordersource_code,
       max(ordersource_code) as ordersource_name,
       0 as is_stage_cn,
       0 as is_stage_dd,
       0 as is_stage_other,
       999 as is_reback,
       max(fran_code) fran_code,
       max(fran_name) fran_name,
       sign_big sign_big,
      0 as sign_big_count,
       max(t.city_id) as city_id,
       max(t.city_desc) as city_desc,
       max(t.area_id) as area_id,
       max(t.area_desc) as area_desc ,
       0 as pp_sign_cn,  --收派员签收票数
       0 as pp_door_cn,  --收派员上门票数
       0 as pp_stage_cn,  --驿站签收票数
       0 as is_stage_tx,  --送入驿站上门票数【兔喜】
       0 as is_stage_ll,  --送入驿站上门票数【邻里】
       0 as store_in_total,
       max(scan_user) as scan_user,
       scan_user_code as scan_user_code,
       store_code as store_code,
       max(store_name) as store_name,
       0 as should_num,
       0 as should_door_num,
       0 as no_door_yz_num ,
       0 as no_door_jk_num  ,
       max(t.is_dk) as is_dk,
       dt
from jms_dm.dm_door_sign_rate_detail_dt t
where   t.dt = '{{ execution_date | cst_ds }}'
  group by t.dt,scan_site_code,signed_type,store_code,scan_user_code,ordersource_code,sign_big

 distribute by pmod(hash(rand()), 5);
